#!/usr/bin/python2.6
# -*- coding: utf-8 -*-

import urllib2
import html5lib
from html5lib import treebuilders
from BeautifulSoup import BeautifulSoup
import os
import re
#from BeautifulSoup import BeautifulSoup
#import sys
import codecs
import MySQLdb
#sys.setdefaultencoding("UTF8")
#streamWriter = codecs.lookup('utf-8')[-1]
#sys.stdout = streamWriter(sys.stdout)

#sys.setdefaultencoding()
parser = html5lib.HTMLParser(tree=treebuilders.getTreeBuilder("beautifulsoup"))
soup = parser.parse(urllib2.urlopen("http://a-el.ru/catalog.htm").read())

#soup = parser.parse(unicode(open("catalog.htm").read(), 'cp1251'))
table_soup = soup.table.tbody.tr.td.table.tbody.tr.nextSibling.nextSibling.tbody
#print soup.contents[0].name


trs = table_soup.findAll(name = "tr")
th_find_re = re.compile ('\<th.*\>(?P<header>.*?)\</th\>')
td_find_re = re.compile ('\<td.*\>(?P<header>.*?)\</td\>')
tr_re = re.compile('\<tr\>(?P<header>.*)\</tr\>')
i=3
j=4
category_id = 100
product_id = 101
#db= MySQLdb.connect(host='dom28.ath.cx', user='root', passwd='oS0eegeL', db='test2')
db= MySQLdb.connect(host='213.252.92.10', user='root', passwd='oS0eegeL', db='test4')
#db.escape_string()
#MySQLdb.escape_string()
db.set_character_set('utf8')
cursor= db.cursor()
#cursor.execute("SELECT * FROM `jos_vm_product`")
for tr in trs:
	#if trs[i]
	#td_split = td_split_re.split(str(trs[i]))
	#for split in td_split:
	#	print split
	if th_find_re.search(str(trs[i])) != None:
#		print "***************"
#		print th_find_re.search(str(trs[i])).group('header')
#		print "***************"
		category_name = th_find_re.search(str(trs[i])).group('header')
		category_id=category_id+1
		#Создаем новую категорию
		print category_name + "  " + str(category_id)
		cursor.execute("INSERT INTO `jos_vm_category` (`category_id`, `vendor_id`, `category_name`, `category_description`, `category_thumb_image`, `category_full_image`, `category_publish`, `cdate`, `mdate`, `category_browsepage`, `products_per_row`, `category_flypage`, `list_order`) VALUES("+str(category_id)+", 1, '"+category_name+"', '"+category_name+"', '', '', 'Y', 1258969273, 1258969293, 'managed', 1, 'flypage.tpl', 1)")
		cursor.execute("INSERT INTO `jos_vm_category_xref` (`category_parent_id`, `category_child_id`, `category_list`) VALUES(0, +"+str(category_id)+", NULL)")
	else:
#		other_soup =  parser.parse(unicode(str(trs[i]),'utf-8'))
		other_soup = BeautifulSoup(''.join(unicode(str(trs[i]),'utf-8')))
		product = ""
		small_img = ""
		big_img = ""
		try:
			print other_soup.tr.td.a.img['alt']
			print other_soup.tr.td.a.img['src']
			print other_soup.tr.td.a['href']
			product = str(other_soup.tr.td.a.img['alt']) #Модель продукта например CT-100
			small_img = str(other_soup.tr.td.a.img['src']) #картинка предпросмотра
			big_img = str(other_soup.tr.td.a['href']) #большая картинка
		except:
			product = "" #Левая часть продукта
			small_img = "" #картинка предпросмотра
			big_img = "" #большая картинка
#			print "Нет изображения"
		try:
			print str(other_soup.tr.td.nextSibling.nextSibling.string).encode("utf-8")
#			product = str(other_soup.tr.td.nextSibling.nextSibling.string) + " - " + product #правая часть продукта
			small_text = str(other_soup.tr.td.nextSibling.nextSibling.string)  # Краткое описание продукта
		except:
			break
		if other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string != None:
			text = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string)
			print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
			
		else:
			print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling).encode("utf-8")
			text = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling)
		
		print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
		print str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string).encode("utf-8")
		vendor = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string) #Производитель(нужно доделать запрос
		price = str(other_soup.tr.td.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.nextSibling.string) # Цена
		product_id = product_id + 1
		#Добавление продукта
#		sql = "INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
#		cursor.execute(sql, (MySQLdb.escape_string(str(product_id)), "1", "0", MySQLdb.escape_string(str(product_id)), MySQLdb.escape_string(str(small_text)), MySQLdb.escape_string(str(text)), MySQLdb.escape_string(str("http://a-el.ru/"+small_img)), MySQLdb.escape_string(str("http://a-el.ru/"+big_img)), "Y", "0.0000", "шт.", "0.0000", "0.0000", "0.0000", "шт.", "", "0, 1258934400", "", "N", "0", "NULL", "1258969371", "1258969741", MySQLdb.escape_string(str(product)), "0", "", "", "0", "шт.", "0", "N,N,N,N,N,Y,20%,10%", "none,0,0,1", "", "0,0"))
		cursor.execute("INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES ("+str(product_id)+", 1, 0, '"+str(product_id)+"', '"+small_text+"','"+MySQLdb.escape_string(text)+"', 'http://a-el.ru/"+small_img+"', 'http://a-el.ru/"+big_img+"', 'Y', 0.0000, 'шт.', 0.0000, 0.0000, 0.0000, 'шт.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, '"+product+"', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0')")
		#Добавление продукта в категорию созданную ранее
		cursor.execute("INSERT INTO `jos_vm_product_category_xref` (`category_id` ,`product_id` ,`product_list`) VALUES ('"+str(category_id)+"', '"+str(product_id)+"', '1')") 
		#Добавление цены для текущего продукта
		cursor.execute("INSERT INTO `jos_vm_product_price` (`product_price_id`, `product_id`, `product_price`, `product_currency`, `product_price_vdate`, `product_price_edate`, `cdate`, `mdate`, `shopper_group_id`, `price_quantity_start`, `price_quantity_end`) VALUES("+str(product_id)+", "+str(product_id)+", "+price+", 'RUB', 0, 0, 1258969371, 1258969741, 5, 0, 0)")
	i=i+1
#		sql = "INSERT INTO sometable (col1, col2) VALUES (%s, %s)"
#		cursor.execute(sql, (MySQLdb.escape_string(val1),  MySQLdb.escape_string(val2))
#		print type(trs[i])
#		print tr_re.search(str(trs[i]))
#		print td_find_re.findall(str(trs[i]))
#	print str(trs[i])
	i = i + 1
#	if i == 100:
#		break
#INSERT INTO `jos_vm_product` (`product_id`, `vendor_id`, `product_parent_id`, `product_sku`, `product_s_desc`, `product_desc`, `product_thumb_image`, `product_full_image`, `product_publish`, `product_weight`, `product_weight_uom`, `product_length`, `product_width`, `product_height`, `product_lwh_uom`, `product_url`, `product_in_stock`, `product_available_date`, `product_availability`, `product_special`, `product_discount_id`, `ship_code_id`, `cdate`, `mdate`, `product_name`, `product_sales`, `attribute`, `custom_attribute`, `product_tax_id`, `product_unit`, `product_packaging`, `child_options`, `quantity_options`, `child_option_ids`, `product_order_levels`) VALUES (3, 1, 0, '3', 'краткое описание','описание', 'http://a-el.ru/img/al_b16_s.gif', 'http://www.a-el.ru/img/al_b16.jpg', 'Y', 0.0000, 'кг.', 0.0000, 0.0000, 0.0000, 'см.', '', 0, 1258934400, '', 'N', 0, NULL, 1258969371, 1258969741, 'AL-B16', 0, '', '', 0, 'шт.', 0, 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '', '0,0'),
#INSERT INTO `jos_vm_category` (`category_id`, `vendor_id`, `category_name`, `category_description`, `category_thumb_image`, `category_full_image`, `category_publish`, `cdate`, `mdate`, `category_browsepage`, `products_per_row`, `category_flypage`, `list_order`) VALUES(1, 1, 'Безопасность', '<p>Индивидуальные системы безопасности, беспроводные бэби-мониторы, охранное видеонаблюдение, беспроводные охранные системы, персональные металлодетекторы, сирены, автоаксессуары.</p>', 'resized/_________________4b0a58c685ed7_90x90.gif', 'http://www.a-el.ru/img/secure.gif', 'Y', 1258969273, 1258969293, 'managed', 1, 'flypage.tpl', 1),
#sql = "INSERT INTO sometable (col1, col2) VALUES (%s, %s)"
#cursor.execute(sql, (MySQLdb.escape_string(val1),  MySQLdb.escape_string(val2))
#for tag in table_soup:
#	if tag.contents[0].name == th:
	#print str(tag.contents[0].name)
#	next = table_soup.findNextSiblings('tr')
#	if next.contents[0].name == th:
#		print next.contents[0]